package dao;

import modle.ItemInformation;
import modle.JDBCUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ItemInformationDao {
    private Connection connection = null;

    public boolean insertItem(String Name, int Category, int isFinals, String Introduction) {
        ItemInformation Item = new ItemInformation();
        PreparedStatement pstmt = null;//声明一个PreparedStatement对象并将其初始化为null
        String sql = "INSERT\n" +
                "INTO sportitem(SI_Name, SI_Category,SI_IsFinals,SI_Introduction)\n" +
                "VALUES\n" +
                "(?, ?, ?, ?)";//参数占位符
        ResultSet rs = null;
        JDBCUtil db = new JDBCUtil();
        try {
            connection = JDBCUtil.getConn();
            pstmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            pstmt.setString(1, Name);
            pstmt.setInt(2, Category);
            pstmt.setInt(3, isFinals);
            pstmt.setString(4, Introduction);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            return false;
        } finally {
            try {
                if (connection != null && (!connection.isClosed())) {
                    connection.close();
                }
            } catch (SQLException e) {
                //e.printStackTrace();
            }
        }
        return true;
    }

    public boolean updateItem(int ID, String Name, int Category, int isFinals, String Introduction) {
        PreparedStatement pstmt = null;//声明一个PreparedStatement对象并将其初始化为null
        String sql = "UPDATE sportitem SET SI_Name = ?, SI_Category = ?, SI_isFinals = ?, SI_Introduction = ? WHERE SI_ID = ? ";//参数占位符
        ResultSet rs = null;
        JDBCUtil db = new JDBCUtil();
        int result = 0, status = 0;
        try {
            connection = db.getConn();
            pstmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            //ResultSet.TYPE_SCROLL_INSENSITIVE：游标可以前后移动，结果集对创建结果集后其他人对数据库所做的更改不敏感
            // 结果集是只读的，这是默认的并发类型
            pstmt.setString(1, Name);
            pstmt.setInt(2, Category);
            pstmt.setInt(3, isFinals);
            pstmt.setString(4, Introduction);
            pstmt.setInt(5, ID);
            pstmt.executeUpdate();
            pstmt.close();
            connection.close();
        }  catch (SQLException e) {
            //e.printStackTrace();
        } catch (NullPointerException e) {
            return false;
        }
        finally {
            try {
                if (connection != null && (!connection.isClosed())) {
                    connection.close();
                }
            } catch (SQLException e) {
                //e.printStackTrace();
            }
        }
        return true;
    }

    //0为数据库错误，1为有完整性约束，2为成功
    public int deleteItem(int ID) {
        PreparedStatement pstmt = null;//声明一个PreparedStatement对象并将其初始化为null
        String sql = "delete from sportitem WHERE SI_ID = ? ";//参数占位符
        ResultSet rs = null;
        JDBCUtil db = new JDBCUtil();
        try {
            connection = db.getConn();
            pstmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            //ResultSet.TYPE_SCROLL_INSENSITIVE：游标可以前后移动，结果集对创建结果集后其他人对数据库所做的更改不敏感
            // 结果集是只读的，这是默认的并发类型
            pstmt.setInt(1, ID);
            pstmt.executeUpdate();
            pstmt.close();
            connection.close();
        } catch (SQLIntegrityConstraintViolationException e) {
            return 1;
        } catch (SQLException e) {
            //e.printStackTrace();
        } catch (NullPointerException e) {
            return 0;
        }
        finally {
            try {
                if (connection != null && (!connection.isClosed())) {
                    connection.close();
                }
            } catch (SQLException e) {
                //e.printStackTrace();
            }
        }
        return 2;
    }

    public boolean deleteItemAndRecord(int ID) {
        PreparedStatement pstmt = null;//声明一个PreparedStatement对象并将其初始化为null
        String sql = "CALL C_P_DeleteItemAndRecord(?)";//参数占位符
        ResultSet rs = null;
        JDBCUtil db = new JDBCUtil();
        try {
            connection = db.getConn();
            pstmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            //ResultSet.TYPE_SCROLL_INSENSITIVE：游标可以前后移动，结果集对创建结果集后其他人对数据库所做的更改不敏感
            // 结果集是只读的，这是默认的并发类型
            pstmt.setInt(1, ID);
            pstmt.execute();
            pstmt.close();
            connection.close();
        } catch (SQLIntegrityConstraintViolationException e) {
            return false;
        } catch (SQLException e) {
            //e.printStackTrace();
        } catch (NullPointerException e) {
            return false;
        }
        finally {
            try {
                if (connection != null && (!connection.isClosed())) {
                    connection.close();
                }
            } catch (SQLException e) {
                //e.printStackTrace();
            }
        }
        return true;
    }

    public List<ItemInformation> getSearchItemInformation() {
        List<ItemInformation> list = new ArrayList<>();
        PreparedStatement pstmt = null;//声明一个PreparedStatement对象并将其初始化为null
        StringBuilder sql = new StringBuilder("select * from sportitem WHERE 1=1");//参数占位符

        ResultSet rs = null;
        try{
            connection = JDBCUtil.getConn();
            pstmt = connection.prepareStatement(sql.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            //ResultSet.TYPE_SCROLL_INSENSITIVE：游标可以前后移动，结果集对创建结果集后其他人对数据库所做的更改不敏感
            // 结果集是只读的，这是默认的并发类型
            rs = pstmt.executeQuery();
            int num = 1;
            while (rs.next()) {
                ItemInformation item = new ItemInformation();
                item.setId(rs.getInt("SI_ID"));
                item.setNum(num++);//设置项目序号
                item.setName(rs.getString("SI_NAME"));
                item.setCategory(rs.getInt("SI_Category"));
                item.setIsFinals(rs.getInt("SI_IsFinals"));
                item.setIntroduction(rs.getString("SI_Introduction"));

                list.add(item);
            }
            pstmt.close();
            connection.close();
        }catch (SQLException e) {
            //e.printStackTrace();
        }
        catch (NullPointerException e){
            return null;
        }finally{
            try{
                if (connection != null && (!connection.isClosed())){
                    connection.close();
                }
            }catch(SQLException e){
                //e.printStackTrace();
            }
        }
        return list;
    }
}

